package org.brownflat.watergate.resources.mysql;

import org.brownflat.watergate.resources.MySqlUpgrade;
import org.springframework.stereotype.Component;

/**
 * <p>&copy Rupert Jones 2011,2012</p>
 *
 * @author rup
 */
@Component
public class Upgrade0009ReviewStructure implements MySqlUpgrade {

    private static final int VERSION = 9;

    @Override
    public int getSequence() {
        return VERSION;
    }

    @Override
    public String getSql() {
          return "CREATE TABLE `wgate_review` (\n"
                + " `id` int(11) NOT NULL AUTO_INCREMENT,\n"
                + " `title` text NOT NULL,\n"
                + " `creator` int(11) NOT NULL,\n"
                + " `review_state` int(11) NOT NULL,\n"
                + " `created_at` timestamp NOT NULL,\n"
                + " `updated_at` timestamp NOT NULL,\n"
                + "  PRIMARY KEY (`id`)\n"
                + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;\n"
                + "\n"
                + "CREATE TABLE `wgate_review_state`(\n"
                + " `id` int(11) NOT NULL AUTO_INCREMENT,\n"
                + "  `name` varchar(120) NOT NULL,\n"
                + " `created_at` timestamp NOT NULL,\n"
                + " `updated_at` timestamp NOT NULL,\n"
                + "  PRIMARY KEY (`id`)\n"
                + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;\n"
                + "\n"
                + "CREATE TABLE `wgate_review_contributor`(\n"
                + " `id` int(11) NOT NULL AUTO_INCREMENT,\n"
                + "  `review_id` int(11) NOT NULL,\n"
                + "  `user_id` int(11) NOT NULL,\n"
                + "  PRIMARY KEY (`id`),\n"
                + "  UNIQUE KEY `rev_contr_UNIQUE` (`review_id`, `user_id`)\n"
                + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;\n"
                + "\n"
                + "CREATE TABLE `wgate_review_commit`(\n"
                + " `id` int(11) NOT NULL AUTO_INCREMENT,\n"
                + " `review_id` int(11) NOT NULL,\n"
                + " `commit_id` int(11) NOT NULL,\n"
                + "  PRIMARY KEY (`id`)\n"
                + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;\n"
                + "\n"
                + "CREATE TABLE `wgate_review_comment`(\n"
                + " `id` int(11) NOT NULL AUTO_INCREMENT,\n"
                + " `review_id` int(11) NOT NULL,\n"
                + " `comment_id` int(11) NOT NULL,\n"
                + "  PRIMARY KEY (`id`)\n"
                + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;\n"
                + "\n"
                + "CREATE TABLE `wgate_comment`(\n"
                + " `id` int(11) NOT NULL AUTO_INCREMENT,\n"
                + " `user_id` int(11) NOT NULL,\n"
                + " `comment` text NOT NULL,\n"
                + "  `created_at` timestamp NOT NULL,\n"
                + "  `updated_at` timestamp NOT NULL,\n"
                + "  PRIMARY KEY (`id`)\n"
                + ") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;\n"
                + " ALTER TABLE wgate_comment ADD COLUMN (parent_id int(11) NOT NULL default 0);\n"
                + "INSERT INTO wgate_review_state (name, created_at, updated_at) VALUES ('COMMENCED', current_timestamp, current_timestamp);\n"
                + "INSERT INTO wgate_review_state (name, created_at, updated_at) VALUES ('COMPLETED', current_timestamp, current_timestamp);\n"
                + "INSERT INTO wgate_review_state (name, created_at, updated_at) VALUES ('CLOSED', current_timestamp, current_timestamp);\n"
                + "INSERT INTO wgate_review_state (name, created_at, updated_at) VALUES ('ARCHIVED', current_timestamp, current_timestamp);\n";
    }

    @Override
    public String getName() {
        return "Create review structure";
    }
}
